from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template


def query_NAV_series(start_date, end_date, entity_list=None, is_per_unit=True, is_official=True):
	
	sql_tpl = Template('''select entity,ref_date,${COLUMN}
						  from ${TABLE}
						where ref_date >= TO_DATE('${START}', 'yyyy-mm-dd')
						   and ref_date <= TO_DATE('${END}', 'yyyy-mm-dd')
						   and type = 'NAV'
						   and ${ENTITY_CRITERIA}
						order by entity,ref_date
						''')	
	
	table_text = '''(select * from asset_value where official = 'F') '''
	if is_official:
		table_text = '''(select * from official_asset_value where is_published = 'T') '''
	
	column_text = 'av'
	if is_per_unit:
		column_text = 'avpu'
	
	sql_text = sql_tpl.substitute(COLUMN = column_text,
								START = start_date.isoformat(),
								END = end_date.isoformat(),
								ENTITY_CRITERIA = format_sql_criteria('entity', entity_list), 
								TABLE = table_text)	
	
	cursor = DBConnFactory().get_db_connection('ACCOUNTING').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()

def query_latest_NAV(ref_date, entity_list=None, is_per_unit=True):
	
	sql_tpl = Template('''select av.entity, av.ref_date, ${COLUMN}
						  from asset_value_ex av,	
							(select icpt.entity as entity, NVL(ld.ldate, icpt.d) as ldate
							from (select entity, min(ref_date) d
								  from asset_value_ex
								 where ${ENTITY_CRITERIA}
								 group by entity) icpt
							left join
							(select entity, max(ref_date) ldate
							from asset_value_ex
								 where ref_date <= TO_DATE('${DATE}', 'yyyy-mm-dd')
								   and ${ENTITY_CRITERIA}
								 group by entity) ld
							on ld.entity = icpt.entity) lid
						 where av.entity = lid.entity
						   and av.ref_date = lid.ldate
						   and av.type = 'NAV' ''')					   
					
	column_text = 'av.av'
	if is_per_unit:
		column_text = 'av.avpu'
	
	sql_text = sql_tpl.substitute(COLUMN = column_text,
								DATE = ref_date.isoformat(),
								ENTITY_CRITERIA = format_sql_criteria('entity', entity_list))
	
	cursor = DBConnFactory().get_db_connection('ACCOUNTING').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	
def query_inception_NAV(entity_list=None, is_per_unit=True):
	
	sql_tpl = Template('''select av.entity, av.ref_date, ${COLUMN}
						  from official_asset_value av,
							   (select entity, min(ref_date) d
								  from official_asset_value
								 where ${ENTITY_CRITERIA}
								 group by entity) icpt
						 where av.entity = icpt.entity
						   and av.ref_date = icpt.d
						   and av.type = 'NAV' ''')
	column_text = 'av.av'
	if is_per_unit:
		column_text = 'av.avpu'
	
	sql_text = sql_tpl.substitute(COLUMN = column_text,
								ENTITY_CRITERIA = format_sql_criteria('entity', entity_list))

	cursor = DBConnFactory().get_db_connection('ACCOUNTING').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	
def query_post_AV(ref_date, portf_list=None):

	sql_tpl = Template('''select portfolio_id, mkt_val_post from portfolio_mkt_val
								where ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')
								and ${PORTF_CRITERIA}
								order by portfolio_id''')
	
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(), 
								PORTF_CRITERIA = format_sql_criteria('portfolio_id', portf_list))
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	
def query_pref_AV(ref_date, portf_list=None):

	sql_tpl = Template('''select pv.portfolio_id, 
								 pv.mkt_val_post - NVL(sr.amt,0)
							from portfolio_mkt_val pv
							left join
								(select parent,ref_date,sum(decode(type,'subscription',1,-1)*amount) amt 
								from amount_chg_ex@ACCT_DB group by parent,ref_date) sr 
								on sr.parent=pv.portfolio_id and sr.ref_date=pv.ref_date
							where pv.ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')
							and ${PORTF_CRITERIA}
							order by pv.portfolio_id''')
	
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(), 
								PORTF_CRITERIA = format_sql_criteria('pv.portfolio_id', portf_list))

	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()













